package user.dao;

import java.sql.Connection;
import java.sql.Date;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Calendar;
import java.util.List;

import user.db.DBConnection;
import user.model.Book;
import user.model.Borrow;
import user.model.User;

/**
 * 用户Dao类
 * 
 * @author 摘星者
 *
 */
public class UserDao {
	/**
	 * 登录验证
	 * 
	 * @param con
	 * @param user
	 * @return
	 * @throws Exception
	 */
	public User login(Connection con, User user) throws Exception {
		User resultUser = null;
		String sql = "select * from user where userName = ? and password = ?";
		PreparedStatement ps = con.prepareStatement(sql);
		ps.setString(1, user.getUserName());
		ps.setString(2, user.getPassword());
		ResultSet rs = ps.executeQuery();
		if (rs.next()) {
			resultUser = new User();
			resultUser.setUserId(rs.getInt("id"));
			resultUser.setUserName(rs.getString("userName"));
			resultUser.setPassword(rs.getString("password"));
		}
		return resultUser;

	}

	/**
	 * 注册，添加新用户
	 * 
	 * @param con
	 * @param userMessage
	 * @return
	 * @throws Exception
	 */
	public int add(Connection con, User userMessage) throws Exception {

		// 先在数据库中查询主键id，若查到了，返回0，代表注册失败
		String sqlid = "select * from user where id = ?";
		PreparedStatement pstmtid = con.prepareStatement(sqlid);
		pstmtid.setInt(1, userMessage.getUserId());
		ResultSet rsid = pstmtid.executeQuery();
		if (rsid.next()) {
			return 0;
		}
		// 若数据库中没有id，则可以插入
		String sql = "insert into user values(?,?,?)";
		PreparedStatement pstmt = con.prepareStatement(sql);

		pstmt.setInt(1, userMessage.getUserId());
		pstmt.setString(2, userMessage.getUserName());
		pstmt.setString(3, userMessage.getPassword());

		return pstmt.executeUpdate();
	}

	public int updatePwd(String newpwd1, String loginName) {
		String sql = "update user set password=? where username=?";
		Connection con = DBConnection.getConnection();
		PreparedStatement ps = null;
		ResultSet rs = null;
		int i = 0;
		try {
			ps = con.prepareStatement(sql);
			ps.setString(1, newpwd1);
			ps.setString(2, loginName);
			i = ps.executeUpdate();
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			DBConnection.close(rs, ps);
		}
		return i;
	}

	/**
	 * 用户的书架
	 * 
	 * @param id
	 * @return
	 */
	public List<Book> listBook(String id) {
		String sql = "select * from book,borrow where book.id = borrow.bookid and userid = ? and rtime is null";
		PreparedStatement ps = null;
		ResultSet rs = null;
		Connection con = null;
		con = DBConnection.getConnection();
		List<Book> list = new ArrayList<Book>();
		try {
			ps = con.prepareStatement(sql);
			ps.setString(1, id);
			rs = ps.executeQuery();
			while (rs.next()) {
				String bookName = rs.getString("name");
				String author = rs.getString("author");
				Date bdate = rs.getDate("btime");
				Date sdate = rs.getDate("stime");
				Book book = new Book(bookName, author, bdate, sdate);
				list.add(book);
			}
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			DBConnection.close(rs, ps);
		}

		return list;
	}

	public User selectUname(String loginName) {
		String sql = "select id from user where username = ?";
		PreparedStatement ps = null;
		ResultSet rs = null;
		Connection con = null;
		con = DBConnection.getConnection();
		User user = null;
		try {
			ps = con.prepareStatement(sql);
			ps.setString(1, loginName);
			rs = ps.executeQuery();
			if (rs.next()) {
				String id = rs.getString("id");
				user = new User(id);
				user.setId(id);

			}
		} catch (Exception e) {
			e.printStackTrace();
		}
		return user;
	}

	public Book selectBookId(String bookName) {
		String sql = "select * from book where name = ?";
		PreparedStatement ps = null;
		ResultSet rs = null;
		Connection con = null;
		con = DBConnection.getConnection();
		Book book = null;
		try {
			ps = con.prepareStatement(sql);
			ps.setString(1, bookName);
			rs = ps.executeQuery();
			while (rs.next()) {
				int id = rs.getInt("id");
				String name = rs.getString("name");
				String author = rs.getString("author");
				book = new Book(id, name, author);

			}
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			DBConnection.close(rs, ps);
		}
		return book;
	}

	public int updateBookLend(int bookid) {
		String sql = "update book set lend = 1 where id = ?";
		Connection con = DBConnection.getConnection();
		PreparedStatement ps = null;
		ResultSet rs = null;
		int i = 0;
		try {
			ps = con.prepareStatement(sql);
			ps.setInt(1, bookid);
			i = ps.executeUpdate();
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			DBConnection.close(rs, ps);
		}
		return i;
	}

	public int updateRtime(String userId, int bookid) {
		String sql = "update borrow set rtime = ? where userid = ? and bookid = ?";
		Connection con = DBConnection.getConnection();
		PreparedStatement ps = null;
		ResultSet rs = null;
		int i = 0;
		try {
			ps = con.prepareStatement(sql);
			ps.setDate(1, new Date(System.currentTimeMillis()));
			ps.setString(2, userId);
			ps.setInt(3, bookid);
			i = ps.executeUpdate();
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			DBConnection.close(rs, ps);
		}
		return i;
	}

	public List<Book> listBookHistory(String userId) {
		String sql = "select book.name,book.author,borrow.btime,borrow.rtime from book,borrow where book.id = borrow.bookid and userid =? and rtime is not null";
		PreparedStatement ps = null;
		ResultSet rs = null;
		Connection con = null;
		con = DBConnection.getConnection();
		List<Book> list = new ArrayList<Book>();
		try {
			ps = con.prepareStatement(sql);
			ps.setString(1, userId);
			rs = ps.executeQuery();
			while (rs.next()) {
				String bookName = rs.getString("name");
				String author = rs.getString("author");
				Date bdate = rs.getDate("btime");
				Date rdate = rs.getDate("rtime");
				Book book = new Book(bookName, author, bdate, rdate);
				list.add(book);
			}
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			DBConnection.close(rs, ps);
		}
		return list;
	}

	public Borrow selectTimes(String userId, int bookid) {
		String sql = "select times from borrow where userid = ? and bookid = ? and rtime is null";
		Connection con = DBConnection.getConnection();
		PreparedStatement ps = null;
		ResultSet rs = null;
		Borrow borrow = null;
		try {
			ps = con.prepareStatement(sql);
			ps.setString(1, userId);
			ps.setInt(2, bookid);
			rs = ps.executeQuery();
			if (rs.next()) {
				int times = rs.getInt("times");
				borrow = new Borrow(times);
				borrow.setTimes(times);
			}
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			DBConnection.close(rs, ps);
		}
		return borrow;

	}

	public int renewBook(String userId, int bookid) {
		String sql = "update borrow set times = 1 where userid = ? and bookid = ?";
		Connection con = DBConnection.getConnection();
		PreparedStatement ps = null;
		ResultSet rs = null;
		int i = 0;
		try {
			ps = con.prepareStatement(sql);
			ps.setString(1, userId);
			ps.setInt(2, bookid);
			i = ps.executeUpdate();
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			DBConnection.close(rs, ps);
		}
		return i;
	}

	public int renewBooktwo(String userId, int bookid) {
		String sql = "update borrow set times = 2 where userid = ? and bookid = ?";
		Connection con = DBConnection.getConnection();
		PreparedStatement ps = null;
		ResultSet rs = null;
		int i = 0;
		try {
			ps = con.prepareStatement(sql);
			ps.setString(1, userId);
			ps.setInt(2, bookid);
			i = ps.executeUpdate();
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			DBConnection.close(rs, ps);
		}
		return i;
	}

	public int renewStime(String userId, int bookid) {
		Connection con = DBConnection.getConnection();
		PreparedStatement ps = null;
		ResultSet rs = null;
		int i = 0;
		String sqltime = "select stime from borrow where userid = ? and bookid = ?";
		try {
			ps = con.prepareStatement(sqltime);
			ps.setString(1, userId);
			ps.setInt(2, bookid);
			rs = ps.executeQuery();
			while (rs.next()) {
				Date date = rs.getDate("stime");
				Calendar calendar = Calendar.getInstance();
				calendar.setTime(date);
				calendar.add(Calendar.MONTH, 1);
				java.util.Date utilDate = calendar.getTime();
				java.sql.Date sqlDate = new java.sql.Date(utilDate.getTime());
				String sql = "update borrow set stime = ? where userid = ? and bookid = ?";
				try {
					ps = con.prepareStatement(sql);
					ps.setDate(1, sqlDate);
					ps.setString(2, userId);
					ps.setInt(3, bookid);
					i = ps.executeUpdate();
				} catch (Exception e) {
					e.printStackTrace();
				} 
			}
		} catch (Exception e) {
			e.printStackTrace();
		}finally {
			DBConnection.close(rs, ps);
		}
		return i;
	}

}
